package com.escom.dao;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Created by Carlos on 04/04/2014.
 */
public class DataBaseManager extends SQLiteOpenHelper {

    //TABLA PERSONA
    public static final String HLC_PERSONA_TAB = "hlc_persona_tab";
    public static final String ID_PERSONA = "id_persona";
    public static final String NOMBRE = "nombre";
    public static final String APELLIDO_PATERNO = "apellido_paterno";
    public static final String APELLIDO_MATERNO = "apellido_materno";
    public static final String TELEFONO = "telefono";
    public static final String CELULAR = "celular";
    public static final String EMAIL = "email";
    public static final String GENERO = "genero";
    public static final String COLONIA = "colonia";
    public static final String CIUDAD = "ciudad";
    public static final String ESTADO = "estado";
    public static final String CP = "cp";

    //TABLA PACIENTE
    public static final String HLC_PACIENTE_TAB = "hlc_paciente_tab";
    public static final String ID_PACIENTE = "id_paciente";
    public static final String CURP = "curp";
    public static final String TIPO_SANGRE = "tipo_sangre";
    public static final String FECHA_NACIMIENTO = "fecha_nacimiento";
    public static final String LUGAR_NACIMIENTO = "lugar_nacimiento";
    public static final String NO_SS = "no_ss";
    public static final String PESO = "peso";
    public static final String ESTATURA = "estatura";

    //TABLA USUARIO
    public static final String HLC_USUARIO_TAB = "hlc_usuario_tab";
    public static final String ID_USUARIO = "Id_usuario";
    public static final String ACTIVO = "activo";
    public static final String NICK = "nick";
    public static final String PASSWORD = "password";
    public static final String ULTIMA_CONEXION = "ultima_conexion";
    public static final String ULTIMA_ACTUALIZACION = "ultima_actualizacion";
    public static final String TIPO_USUARIO = "tipo_usuario";

    //TABLA MEDICO
    public static final String HLC_MEDICO_TAB = "hlc_medico_tab";
    public static final String ID_MEDICO = "id_medico";
    public static final String CEDULA = "cedula";
    public static final String ESPECIALIDAD = "especialidad";

    //TABLA HISTORIA_FAMILIAR
    public static final String HLC_HISTORIA_FAMILIAR_TAB = "hlc_historia_familiar_tab";
    public static final String ID_HISTORIA_FAMILIAR = "id_historia_familiar";

    //TABLA PARENTESCO
    public static final String HLC_PARENTESCO_CAT = "hlc_parentesco_cat";
    public static final String ID_PARENTESCO = "id_parentesco";
    public static final String TIPO_PARENTESCO = "tipo_parentesco";

    //TABLA ENFERMEDAD
    public static final String HLC_ENFERMEDAD_CAT = "hlc_enfermedad_cat";
    public static final String ID_ENFERMEDAD = "id_enfermedad";
    public static final String ENFERMEDAD = "enfermedad";

    //TABLA PACIENTE MEDICO
    public static final String HLC_PACIENTE_MEDICO_TAB = "hlc_paciente_medico_tab";
    public static final String ID_PACIENTE_MEDICO = "id_paciente_medico";

    //TABLA HOSPITAL
    public static final String HLC_HOSPITAL_CAT = "hlc_hospital_cat";
    public static final String ID_HOSPITAL = "id_hospital";
    public static final String HOSPITAL = "hospital";

    //TABLA DIAGNOSTICO
    public static final String HLC_DIAGNOSTICO_TAB = "hlc_diagnostico_tab";
    public static final String ID_DIAGNOSTICO = "id_diagnostico";

    //TABLA PRUEBA MEDICA
    public static final String HLC_PRUEBA_MEDICA_CAT = "hlc_prueba_medica_cat";
    public static final String ID_PRUEBA_MEDICA = "id_prueba_medica";
    public static final String ARCHIVO_PRUEBA = "archivo_prueba";

    //TABLA ARRITMIA
    public static final String HLC_ARRITIMIA_CAT = "hlc_arritmia_cat";
    public static final String ID_ARRITMIA = "id_arritmia";
    public static final String TIPO_ARRITMIA = "tipo_arritmia";

    //TABLA CONSULTAS
    public static final String HLC_CONSULTAS_TAB = "hlc_consultas_tab";
    public static final String ID_CONSULTAS = "id_consultas";
    public static final String FECHA_INICIO = "fecha_inicio";
    public static final String FECHA_FINAL = "fecha_final";
    public static final String DETALLES = "detalles";


    public static final String CREATE_TABLE_PERSONA =
            "create table " + HLC_PERSONA_TAB + " ("
                    + ID_PERSONA + " integer primary key autoincrement,"
                    + NOMBRE + " text not null,"
                    + APELLIDO_PATERNO + " text,"
                    + APELLIDO_MATERNO + " text,"
                    + TELEFONO + " text,"
                    + CELULAR + " text,"
                    + EMAIL + " text,"
                    + GENERO + " text,"
                    + COLONIA + " text,"
                    + CIUDAD + " text,"
                    + ESTADO + " text,"
                    + CP + " text"
                    + ");";

    public static final String CREATE_TABLE_PACIENTE =
            "create table " + HLC_PACIENTE_TAB + " ("
                    + ID_PACIENTE + " integer primary key autoincrement,"
                    + ID_PERSONA + " integer NOT NULL,"
                    + CURP + " text not null,"
                    + FECHA_NACIMIENTO + " text,"
                    + LUGAR_NACIMIENTO + " text,"
                    + NO_SS + " text,"
                    + PESO + " text,"
                    + ESTATURA + " text,"
                    + TIPO_SANGRE + " text,"
                    + "FOREIGN KEY(" + ID_PERSONA + ") REFERENCES " + HLC_PERSONA_TAB + "(" + ID_PERSONA + ")"
                    + ");";

    public static final String CREATE_TABLE_MEDICO =
            "create table " + HLC_MEDICO_TAB + "("
                    + ID_MEDICO + " integer primary key autoincrement,"
                    + ID_PERSONA + " INTEGER NOT NULL,"
                    + ID_USUARIO + " INTEGER NOT NULL, "
                    + CEDULA + " text,"
                    + ESPECIALIDAD + " text,"
                    + "FOREIGN KEY(" + ID_PERSONA + ") REFERENCES " + HLC_PERSONA_TAB + "(" + ID_PERSONA + "),"
                    + "FOREIGN KEY(" + ID_USUARIO + ") REFERENCES " + HLC_USUARIO_TAB + "(" + ID_USUARIO + ")"
                    + ");";

    public static final String CREATE_TABLE_USUARIO =
            "create table " + HLC_USUARIO_TAB + "("
                    + ID_USUARIO + " integer primary key autoincrement,"
                    + ACTIVO + " NUMERIC,"
                    + NICK + " text,"
                    + PASSWORD + " text,"
                    + ULTIMA_CONEXION + " text,"
                    + ULTIMA_ACTUALIZACION + " text,"
                    + TIPO_USUARIO + " text"
                    + ");";

    public static final String CREATE_TABLE_PACIENTE_MEDICO =
            "create table " + HLC_PACIENTE_MEDICO_TAB + "("
                    + ID_PACIENTE_MEDICO + " integer primary key autoincrement,"
                    + ID_PACIENTE + " INTEGER NOT NULL, "
                    + ID_MEDICO + " INTEGER NOT NULL, "
                    + "FOREIGN KEY (" + ID_PACIENTE + ") REFERENCES " + HLC_PACIENTE_TAB + "(" + ID_PACIENTE + "),"
                    + "FOREIGN KEY (" + ID_MEDICO + ") REFERENCES " + HLC_ENFERMEDAD_CAT + "(" + ID_MEDICO + ")"
                    + ");";

    public static final String CREATE_TABLE_HISTORIA_FAMILIAR =
            "create table " + HLC_HISTORIA_FAMILIAR_TAB + "("
                    + ID_HISTORIA_FAMILIAR + " integer primary key autoincrement,"
                    + ID_PACIENTE_MEDICO + " INTEGER NOT NULL, "
                    + ID_PARENTESCO + " INTEGER NOT NULL, "
                    + ID_ENFERMEDAD + " INTEGER NOT NULL, "
                    + "FOREIGN KEY (" + ID_PACIENTE_MEDICO + ") REFERENCES " + HLC_PACIENTE_MEDICO_TAB + "(" + ID_PACIENTE_MEDICO + "),"
                    + "FOREIGN KEY (" + ID_PARENTESCO + ") REFERENCES " + HLC_PARENTESCO_CAT + "(" + ID_PARENTESCO + "), "
                    + "FOREIGN KEY (" + ID_ENFERMEDAD + ") REFERENCES " + HLC_ENFERMEDAD_CAT+ "(" + ID_ENFERMEDAD + ") "
                    + ");";

    public static final String CREATE_TABLE_PARENTESCO =
            "create table " + HLC_PARENTESCO_CAT + "("
                    + ID_PARENTESCO + " integer primary key autoincrement,"
                    + TIPO_PARENTESCO + " text"
                    + ");";

    public static final String CREATE_TABLE_ENFERMEDAD =
            "create table " + HLC_ENFERMEDAD_CAT + "("
                    + ID_ENFERMEDAD + " integer primary key autoincrement,"
                    + ENFERMEDAD + " text"
                    + ");";

    public static final String CREATE_TABLE_CONSULTAS =
            "create table " + HLC_CONSULTAS_TAB + "("
                    + ID_CONSULTAS + " integer primary key autoincrement,"
                    + ID_HOSPITAL + " INTEGER NOT NULL, "
                    + ID_DIAGNOSTICO + " INTEGER, "
                    + ID_PACIENTE_MEDICO + " INTEGER NOT NULL,"
                    + FECHA_INICIO + " text,"
                    + FECHA_FINAL + " text,"
                    + DETALLES + " text,"
                    + "FOREIGN KEY (" + ID_HOSPITAL + ") REFERENCES  " + HLC_HOSPITAL_CAT + "(" + ID_HOSPITAL + "),"
                    + "FOREIGN KEY (" + ID_DIAGNOSTICO + ") REFERENCES  " + HLC_DIAGNOSTICO_TAB + "(" + ID_DIAGNOSTICO + "),"
                    + "FOREIGN KEY (" + ID_PACIENTE_MEDICO + ") REFERENCES  " + HLC_PACIENTE_MEDICO_TAB + "(" + ID_PACIENTE_MEDICO + ")"
                    + ");";

    public static final String CREATE_TABLE_HOSPITAL =
            "create table " + HLC_HOSPITAL_CAT + "("
                    + ID_HOSPITAL + " integer primary key autoincrement,"
                    + HOSPITAL + " text"
                    + ");";

    public static final String CREATE_TABLE_DIAGNOSTICO =
            "create table " + HLC_DIAGNOSTICO_TAB + "("
                    + ID_DIAGNOSTICO + " integer primary key autoincrement,"
                    + ID_ARRITMIA + " INTEGER NOT NULL, "
                    + ID_PRUEBA_MEDICA + " INTEGER NOT NULL, "
                    + "FOREIGN KEY (" + ID_ARRITMIA + ") REFERENCES " + HLC_ARRITIMIA_CAT + "(" + ID_ARRITMIA + ")"
                    + "FOREIGN KEY (" + ID_PRUEBA_MEDICA + ") REFERENCES " + HLC_PRUEBA_MEDICA_CAT + "(" + ID_PRUEBA_MEDICA + ")"
                    + ");";

    public static final String CREATE_TABLE_PRUEBA_MEDICA =
            "create table " + HLC_PRUEBA_MEDICA_CAT + "("
                    + ID_PRUEBA_MEDICA + " integer primary key autoincrement,"
                    + ARCHIVO_PRUEBA + " text);";

    public static final String CREATE_TABLE_ARRITMIA =
            "create table " + HLC_ARRITIMIA_CAT + "("
                    + ID_ARRITMIA + " integer primary key autoincrement,"
                    + TIPO_ARRITMIA + " text"
                    + ");";



    public DataBaseManager(Context context) {
        super(context,"healthLC",null,13);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase)  {
        Log.d("Logger DataBaseManager", "onCreate");
        sqLiteDatabase.execSQL(CREATE_TABLE_PERSONA);
        sqLiteDatabase.execSQL(CREATE_TABLE_USUARIO);
        sqLiteDatabase.execSQL(CREATE_TABLE_PACIENTE);
        sqLiteDatabase.execSQL(CREATE_TABLE_MEDICO);
        sqLiteDatabase.execSQL(CREATE_TABLE_PACIENTE_MEDICO);
        sqLiteDatabase.execSQL(CREATE_TABLE_PARENTESCO);
        sqLiteDatabase.execSQL(CREATE_TABLE_ENFERMEDAD);
        sqLiteDatabase.execSQL(CREATE_TABLE_HISTORIA_FAMILIAR);
        sqLiteDatabase.execSQL(CREATE_TABLE_HOSPITAL);
        sqLiteDatabase.execSQL(CREATE_TABLE_PRUEBA_MEDICA);
        sqLiteDatabase.execSQL(CREATE_TABLE_ARRITMIA);
        sqLiteDatabase.execSQL(CREATE_TABLE_DIAGNOSTICO);
        sqLiteDatabase.execSQL(CREATE_TABLE_CONSULTAS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int versionOld, int versionNew) {
        Log.d("Logger DataBaseManager","onUpgrade");
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_PERSONA_TAB );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_USUARIO_TAB );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_PACIENTE_TAB );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_MEDICO_TAB );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_PACIENTE_MEDICO_TAB );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_PARENTESCO_CAT );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_ENFERMEDAD_CAT );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_HISTORIA_FAMILIAR_TAB );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_HOSPITAL_CAT );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_PRUEBA_MEDICA_CAT );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_ARRITIMIA_CAT );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_DIAGNOSTICO_TAB );
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + HLC_CONSULTAS_TAB );

        onCreate(sqLiteDatabase);
    }
}
